package fr.umlv.m2.recipes.ideas.dao;

import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.Iterator;
import java.util.List;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import fr.umlv.m2.recipes.ideas.bean.IngredientBean;
import fr.umlv.m2.recipes.ideas.dao.interfaces.RecipesConcreteDAOInterface;
import fr.umlv.m2.recipes.ideas.table.enumerations.TableName;

/**
 * DAO uses to handle an ingredient
 * @author Christophe Chillan et Younes Chandarli
 *
 */
public class IngredientDAO implements RecipesConcreteDAOInterface<IngredientBean> {
	private static final String TABLE_INGREDIENT = TableName.INGREDIENT.toString();
	private static final String TABLE_RECIPES = TableName.RECIPES.toString();
	private static final String TABLE_RECIPETOINGREDIENT = TableName.RECIPESTOINGREDIENT.toString();
	
	private static final String COL_ID = "ID";	
	private static final String COL_NAME = "NAME";
	private static final String COL_ENERGY = "ENERGY";
	private static final String COL_PRICE = "PRICE";

	private static final int NUM_COL_ID = 0;
	private static final int NUM_COL_NAME = 1;
	private static final int NUM_COL_ENERGY = 2;
	private static final int NUM_COL_PRICE = 3;
	
	private final StringBuilder CREATE_DB;
	
	
	
	/**
	 * Create an ingredient DAO
	 */
	public IngredientDAO(){
		CREATE_DB = new StringBuilder();
		CREATE_DB.append("CREATE TABLE ");
		CREATE_DB.append(TABLE_INGREDIENT).append(" ");
		CREATE_DB.append("(");
		CREATE_DB.append(COL_ID).append(" INTEGER PRIMARY KEY AUTOINCREMENT, ");
		CREATE_DB.append(COL_NAME).append(" TEXT NOT NULL, ");
		CREATE_DB.append(COL_ENERGY).append(" TEXT, ");
		CREATE_DB.append(COL_PRICE).append(" REAL");
		CREATE_DB.append(");");
	}
	
	/**
	 * @see SQLiteOpenHelper
	 */
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(CREATE_DB.toString());
		
	}

	/**
	 * @see SQLiteOpenHelper
	 */
	public void onUpgrade(SQLiteDatabase db) {
		db.execSQL("DROP TABLE "+ TABLE_INGREDIENT);
		onCreate(db);
		
	}
	
	/**
	 * Insert an ingredient in the database
	 *  @param db
	 *  @param ingredient
	 *  @return long - 1 if the entry has been inserted, 0 otherwise 
	 */
	public long insert(SQLiteDatabase db, IngredientBean ingredient) {
		//Création d'un ContentValues (fonctionne comme une HashMap)
		ContentValues values = new ContentValues();
		//Création d'un ContentValues (fonctionne comme une HashMap)
		if(ingredient.getId()!=-1){
			values.put(COL_ID, ingredient.getId());
		}
		
		values.put(COL_NAME, ingredient.getName());
		values.put(COL_ENERGY, ingredient.getEnergy());
		values.put(COL_PRICE, ingredient.getPrice());
		return db.insert(TABLE_INGREDIENT, null, values);
	}
	
	/**
	 * Insert several ingredients in the database
	 * @param db
	 * @param beans
	 * @return long - the number of entry inserted in the database
	 */
	public long insertAll(SQLiteDatabase db, Collection<IngredientBean> beans) {
		//Création d'un ContentValues (fonctionne comme une HashMap)
		ContentValues values = new ContentValues();
		//Création d'un ContentValues (fonctionne comme une HashMap)
		Iterator<IngredientBean> it = beans.iterator();
		long count = 0;
		while(it.hasNext()){
			IngredientBean ingredient = it.next();
			values.put(COL_NAME, ingredient.getName());
			values.put(COL_ENERGY, ingredient.getEnergy());
			values.put(COL_PRICE, ingredient.getPrice());
			count += db.insert(TABLE_INGREDIENT, null, values);
		}
		return count;
	}

	/**
	 * update an ingredient from the ingredient id in parameters
	 * @param db
	 * @param ingredient
	 * @param id
	 * @return integer - the number of entries uploaded
	 */
	public int update(SQLiteDatabase db, IngredientBean ingredient, int id) {
		//Création d'un ContentValues (fonctionne comme une HashMap)
		ContentValues values = new ContentValues();
		//Création d'un ContentValues (fonctionne comme une HashMap)
		values.put(COL_NAME, ingredient.getName());
		values.put(COL_ENERGY, ingredient.getEnergy());
		values.put(COL_PRICE, ingredient.getPrice());;
		return db.update(TABLE_INGREDIENT,values, COL_ID+ " = " +id, null);
	}

	/**
	 * remove an ingredient from the id
	 * @return integer - the number of entries removed
	 */
	public int removeWithId(SQLiteDatabase db, int id) {
		return db.delete(TABLE_INGREDIENT, COL_ID+ " = "+id, null);
	}
	
	/**
	 * Get the ingredient from its name
	 * @param db
	 * @param name
	 * @return the ingredient in the database
	 */
	public IngredientBean getIngredientWithName(SQLiteDatabase db, String name){
		Cursor c = db.query(TABLE_INGREDIENT, new String[]{COL_ID,COL_NAME,COL_ENERGY,COL_PRICE}, COL_NAME + " LIKE \""+ name + "\"", null, null, null, null);
		return cursorToIngredient(c);
	}
	
	/**
	 * Get All the ingredients from a recipe id
	 * @param db
	 * @param recipeId
	 * @return every ingredient of the related to the recipe id in parameter
	 */
	public List<IngredientBean> getIngredientFromRecipeId(SQLiteDatabase db, int recipeId){
		StringBuilder query = new StringBuilder();
		query.append("SELECT DISTINCT ");
		query.append("i.").append(COL_ID).append(", ");
		query.append("i.").append(COL_NAME).append(", ");
		query.append("i.").append(COL_ENERGY).append(", ");
		query.append("i.").append(COL_PRICE).append(" ");
		query.append("FROM ");
		query.append(TABLE_INGREDIENT).append(" i, ");
		query.append(TABLE_RECIPES).append(" r, ");
		query.append(TABLE_RECIPETOINGREDIENT).append(" ri ");
		query.append("WHERE ");
		query.append("i.").append(COL_ID).append("=").append("ri.ingredient_id ");
		query.append("AND r.id").append("=").append("ri.recipes_id ");
		query.append("AND r.id").append("=").append(recipeId);
		Cursor c = db.rawQuery(query.toString(), null);
		return cursorToAllIngredients(c);
	}
	
	/**
	 * Give all the ingredients in the database
	 * @param db
	 * @return a {@link List} of all the ingredients
	 */
	public List<IngredientBean> getAllIngredients(SQLiteDatabase db){
		Cursor c = db.query(TABLE_INGREDIENT, new String[]{COL_ID,COL_NAME,COL_ENERGY,COL_PRICE}, null, null,
				null, null, null);
		return cursorToAllIngredients(c);
	}
	
	
	/**
	 * Get the last id of the ingredients
	 * @param db
	 * @return integer - the last id inserted in the database
	 */
	public int getLastInsertIdOfIngredient(SQLiteDatabase db) {
		StringBuilder query = new StringBuilder();
		query.append("SELECT max(").append(COL_ID).append(") ");
		query.append("from ").append(TABLE_INGREDIENT);
		Cursor c = db.rawQuery(query.toString(), null);
		return cursorToMaxId(c);
	}
	

	private int cursorToMaxId(Cursor c){
		if(c.getCount()==0){
			return 0;
		}
		c.moveToFirst();
		return c.getInt(NUM_COL_ID);
	}

	private IngredientBean cursorToIngredient(Cursor c) {
		if (c.getCount()==0){
			return null;
		}
		c.moveToFirst();
		
		IngredientBean ingredient = new IngredientBean();
		
		ingredient.setId(c.getInt(NUM_COL_ID));
		ingredient.setName(c.getString(NUM_COL_NAME));
		ingredient.setEnergy(c.getString(NUM_COL_ENERGY));
		ingredient.setPrice(c.getFloat(NUM_COL_PRICE));
		return ingredient;
	}
	
	
	private List<IngredientBean> cursorToAllIngredients(Cursor c) {
		List<IngredientBean> ingredients = new ArrayList<IngredientBean>();
		c.moveToFirst();
		do{
			IngredientBean ingredient = new IngredientBean();
			ingredient.setId(c.getInt(NUM_COL_ID));
			ingredient.setName(c.getString(NUM_COL_NAME));
			ingredient.setEnergy(c.getString(NUM_COL_ENERGY));
			ingredient.setPrice(c.getFloat(NUM_COL_PRICE));
			ingredients.add(ingredient);
			c.moveToNext();
		}while(!c.isAfterLast());
		return Collections.unmodifiableList(ingredients);
	}
	
	

	



	

}
